package com.fsc.fragment.outcome;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.fsc.fragment.DBHelper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class OutComeDao {
	
	//获取系统当前时间
	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	String date = dateFormat.format(new java.util.Date());
	
	private DBHelper dbHelper;
	public OutComeDao(Context context) {
		dbHelper = new DBHelper(context);
	}

	//新增支出
	public void insert(OutCome outCome){
		//得到连接
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		//调用方法执行 insert into OutCome(money,time,type,address,mark)values(?????)
		ContentValues values = new ContentValues();
		values.put("money", outCome.getMoney());
		values.put("time", outCome.getTime());
		values.put("type", outCome.getType());
		values.put("address", outCome.getAddress());
		values.put("mark", outCome.getMark());
		long id = database.insert("OutCome", null, values);
		
		
		outCome.setId((int)id);
		outCome.setTime(date);
		
		//关闭连接
		database.close();
	}
	
	//查询支出
	public List<OutCome> query(){
		
		//得到连接 select * from OutCome
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		
		List<OutCome> list = new ArrayList<OutCome>();
		//调用方法
		Cursor cursor = database.query("OutCome", null, null, null, null, null, "_id desc");
		//从cursor中获取数据封装到list中
		while(cursor.moveToNext()){
			int id = cursor.getInt(0);
			double money = cursor.getDouble(1);
			String time = cursor.getString(2);
			String type = cursor.getString(3);
			String address = cursor.getString(4);
			String mark = cursor.getString(5);
			
			list.add(new OutCome(id,money,time,type,address,mark));
		}
		//关闭连接
		cursor.close();
		database.close();
		
		//返回list
		return list;
	}
	//查询支出
	public List<OutCome> queryTop(){
		
		//得到连接 select * from OutCome
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		
		List<OutCome> list = new ArrayList<OutCome>();
		//调用方法
		//Cursor cursor = database.query("OutCome", null, null, null, null, null, "_id desc");
		//String sql = "select * from OutCome order by _id asc LIMIT 5";
		String sql = "select * from(select * from OutCome order by _id desc LIMIT 5) order by _id desc";
		Cursor cursor = database.rawQuery(sql, null);
		//从cursor中获取数据封装到list中
		while(cursor.moveToNext()){
			int id = cursor.getInt(0);
			double money = cursor.getDouble(1);
			String time = cursor.getString(2);
			String type = cursor.getString(3);
			String address = cursor.getString(4);
			String mark = cursor.getString(5);
			
			list.add(new OutCome(id,money,time,type,address,mark));
		}
		//关闭连接
		cursor.close();
		database.close();
		
		//返回list
		return list;
	}
	//编辑支出
	public void update(OutCome outCome){
		//得到连接
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		//调用方法
		ContentValues values = new ContentValues();
		values.put("money", outCome.getMoney());
		values.put("time", outCome.getTime());
		values.put("type", outCome.getType());
		values.put("address", outCome.getAddress());
		values.put("mark", outCome.getMark());
		database.update("OutCome", values , "_id="+outCome.getId(), null);
		
		outCome.setTime(date);
		//关闭连接
		database.close();
	}
	//删除支出
	public void delete(int id){
		//得到连接
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		//调用方法
		database.delete("OutCome", "_id=?",new String[]{id+""});
		//关闭连接
		database.close();
	}
	
	//根据id查询
	public List<OutCome> query(int id){
		List<OutCome> list = new ArrayList<OutCome>();
		//得到连接 select * from OutCome
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		//调用方法
		Cursor cursor = database.query("OutCome", null, "_id=?", new String[] {id+""}, null, null, null);
		//从cursor中获取数据封装到list中
		while(cursor.moveToNext()){
			int _id=cursor.getInt(0);
			double money = cursor.getDouble(1);
			String time = cursor.getString(2);
			String type = cursor.getString(3);
			String address = cursor.getString(4);
			String mark = cursor.getString(5);
			
			list.add(new OutCome(_id,money,time,type,address,mark));
		}
		//关闭连接
		cursor.close();
		database.close();
		
		//返回list
		return list;
	}
}


